﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Trangtrai.Model;
using TrangTrai.Data.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Trangtrai.Controller.HistoryController
{
    public class TomHisController
    {

        public static List<Cuan> getCua()
        {
            if (ConnectionClass.ConnectToDB())
            {

                SqlDataAdapter userAdapter = new SqlDataAdapter("Select * FROM [CU_AN]", ConnectionClass.sqlConn);


                DataSet user = new DataSet();
                userAdapter.Fill(user, "CU_AN");
                if (user.Tables["CU_AN"].Rows.Count == 0)
                {
                    ConnectionClass.closeConn();
                    return null;
                }
                else
                {
                    List<Cuan> listCuan = new List<Cuan>();
                    foreach (DataRow pRow in user.Tables["CU_AN"].Rows)
                    {
                         listCuan.Add(new Cuan(Int32.Parse(pRow["CU_AN_ID"].ToString()), pRow["CUAN_NAME"].ToString(), Int32.Parse(pRow["CU_AN_TIME"].ToString())));
                    }
                    //SqlDataAdapter roleAdapter = new SqlDataAdapter("Select ROLE_NAME FROM [ROLE] WHERE ROLE_ID = " + roleID, ConnectionClass.sqlConn);
                    ConnectionClass.closeConn();
                    return listCuan;
                }
            }
            return null;
        }
        public static int getMaxValueID()
        {
            int maxValue = -1;
            if (ConnectionClass.ConnectToDB())
            {

                SqlDataAdapter userAdapter = new SqlDataAdapter("select MAX(CHOAN_ID) as maxValue from CHOAN_LOG", ConnectionClass.sqlConn);


                DataSet user = new DataSet();
                userAdapter.Fill(user, "CHOAN");
                if (user.Tables["CHOAN"].Rows.Count == 0)
                {
                    ConnectionClass.closeConn();
                    return -1;
                }
                else
                {
                    foreach (DataRow pRow in user.Tables["CHOAN"].Rows)
                    {
                        maxValue = Int32.Parse(pRow["maxValue"].ToString());
                        break;
                    }
                    ConnectionClass.closeConn();
                    return maxValue;
                }
            }
            return -1;
        }

        public static void insert2Log(string aoName,
                        int age,
                        string note,
            //int choanID,
            //
                        string foodCode,
                        string userName)
        {
            if (ConnectionClass.ConnectToDB())
            {
                SqlCommand insertChoanLog = new SqlCommand("INSERT INTO CHOAN_LOG (AGE, FOOD_CODE, NOTE,AO_ID,CREATED_DATE,CREATED_BY) VALUES ("
                        + age + ",'" + foodCode + "','" + note + "'," + " (SELECT AO_ID FROM AO WHERE AO_NAME = '" + aoName + "')" +","+DateTime.Now.ToString("yyyy-MM-dd")+ ",'admin'" + ")", ConnectionClass.sqlConn);
                insertChoanLog.ExecuteNonQuery();
                ConnectionClass.closeConn();
            }
        }

        public static void insertIntoInfo(double doKiem,
                        double doTrong,
                        double doMan,
                        string mau,
                        string hienTuong,
                        double bod,
                        double cod,
                        double hoaTan,
                        string tomStatus,
                        int tomSize,
                        double phSang,
                        double phChieu)
        {

            int maxValue = getMaxValueID();
            if (ConnectionClass.ConnectToDB())
            {
                String insertToBOD = "INsert into AO_BOD([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES(" +
                    bod + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToBOD, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToCOD = "INsert into AO_COD([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES(" +
                    cod + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToCOD, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToDO = "INsert into AO_DO([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES(" +
                    hoaTan + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToDO, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertTodoTrong = "INsert into AO_DO_TRONG([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES(" +
                    doTrong + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertTodoTrong, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertTodoKiem = "INsert into AO_DOKIEM([VALUE],CHOAN_LOG) VALUES(" +
                    doKiem + "," + maxValue + ")";
                new SqlCommand(insertTodoKiem, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertTodoMan = "INsert into AO_DOMAN([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES(" +
                    doMan + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertTodoMan, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToMau = "INsert into AO_MAU([VALUE],CHOAN_LOG_ID,CREATED_DATE) VALUES('" +
                    mau + "'," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToMau, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToHienTuong = "INsert into HIENTUONG(HIENTUONG_NAME,CHOAN_LOG) VALUES('" +
                    hienTuong + "'," + maxValue + ")";
                new SqlCommand(insertToHienTuong, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToTOMSTATUS = "INsert into AO_TOM_STATUS(TOM_STATUS_NAME,CHOAN_LOG_ID,CREATED_DATE) VALUES('" +
                   tomStatus + "'," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToTOMSTATUS, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToTOMSize = "INsert into AO_TOM_SIZE([VALUE],CHOAN_ID,CREATED_DATE) VALUES(" +
                   tomSize + "," + maxValue + "," + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                new SqlCommand(insertToTOMSize, ConnectionClass.sqlConn).ExecuteNonQuery();

                String insertToPH = "INsert into AO_PH(PH_SANG,PH_CHIEU,CHOAN_ID) VALUES(" +
                                  phSang + "," + phChieu + "," + maxValue + ")";
                new SqlCommand(insertToPH, ConnectionClass.sqlConn).ExecuteNonQuery();
                ConnectionClass.closeConn();
            }
        }

        public static void insertToCuAnHis(String status, int amount, int cuAnID)
        {
            int maxValue = getMaxValueID();
            if (ConnectionClass.ConnectToDB())
            {
                String insertTocuAnHis = "INsert into CU_AN_HIS(STATUS_ID,ACTUAL_AMOUNT,CUAN_ID,CHOAN_ID) VALUES(" +
                    "(SELECT STATUS_ID FROM PARAM_STATUS WHERE STATUS_NAME = '" + status + "')," + amount + "," + cuAnID + "," + maxValue + ")";
                new SqlCommand(insertTocuAnHis, ConnectionClass.sqlConn).ExecuteNonQuery();
                ConnectionClass.closeConn();
            }
        }
        public static void deleteLogInfo(int choanID)
        {
            if (ConnectionClass.ConnectToDB())
            {
                String deleteBOD = "Delete from AO_BOD where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteBOD, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteCOD = "Delete from AO_COD where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteCOD, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteDO = "Delete from AO_DO where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteDO, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteDoTrong = "Delete from AO_DO_TRONG where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteDoTrong, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteKiem = "Delete from AO_DOKIEM where CHOAN_LOG = " + choanID;
                new SqlCommand(deleteKiem, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteMan = "Delete from AO_DOMAN where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteMan, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteMau = "Delete from AO_MAU where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteMau, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deletePH = "Delete from AO_PH where CHOAN_ID = " + choanID;
                new SqlCommand(deletePH, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteSize = "Delete from AO_TOM_SIZE where CHOAN_ID = " + choanID;
                new SqlCommand(deleteSize, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteStatus = "Delete from AO_TOM_STATUS where CHOAN_LOG_ID = " + choanID;
                new SqlCommand(deleteStatus, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteCAHIS = "Delete from CU_AN_HIS where CHOAN_ID = " + choanID;
                new SqlCommand(deleteCAHIS, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteHT = "Delete from HIENTUONG where CHOAN_LOG = " + choanID;
                new SqlCommand(deleteHT, ConnectionClass.sqlConn).ExecuteNonQuery();

                String deleteLOG = "Delete from CHOAN_LOG where CHOAN_ID = " + choanID;
                new SqlCommand(deleteLOG, ConnectionClass.sqlConn).ExecuteNonQuery();

                ConnectionClass.closeConn();
            }
        }
        public String getLastInsertedLog()
        {
            String dateLog = null;
            if (ConnectionClass.ConnectToDB())
            {
                SqlDataAdapter userAdapter = new SqlDataAdapter("SELECT CREATED_DATE as lastDate FROM CHOAN_LOG WHERE CHOAN_ID = (SELECT MAX(CHOAN_ID) FROM CHOAN_LOG)", ConnectionClass.sqlConn);


                DataSet user = new DataSet();
                userAdapter.Fill(user, "CHOAN");
                if (user.Tables["CHOAN"].Rows.Count == 0)
                {
                    ConnectionClass.closeConn();
                    return null;
                }
                else
                {
                    foreach (DataRow pRow in user.Tables["CHOAN"].Rows)
                    {
                        dateLog = pRow["lastDate"].ToString();
                        break;
                    }
                    ConnectionClass.closeConn();
                    return dateLog;
                }
            }
            return null;
        }
    }

}
